package cn.ymatrix.jdbcToYMatrix;

import cn.ymatrix.logger.LoggerLevel;
import cn.ymatrix.logger.MxLogger;
import cn.ymatrix.utils.Config;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.slf4j.Logger;

import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

// 参考资料 https://howtodoinjava.com/java/library/parse-read-write-csv-opencsv/
public class ReadCSVTOYMatrix {

    public static void main(String[] args) {

        MxLogger.loggerLevel(LoggerLevel.INFO);
        Logger LOGGER = MxLogger.init(ReadCSVTOYMatrix.class);

        Connection conn = null;
        long batchSize = Config.getBatchSize();
        long indexTuple = 0;
        try {
            Class.forName(Config.getDRIVER_CLASS_NAME());
            conn = DriverManager.getConnection(Config.getUrl(), Config.getUserName(), Config.getPassWord());

            String SQLINSERT = "INSERT INTO " + Config.getTableName() + " values(?,?,?,?,?,?)";

            conn.setAutoCommit(false);
            PreparedStatement pstmt = conn.prepareStatement(SQLINSERT);
            pstmt.setFetchSize((int) batchSize);

            String filePath = Config.getCsvFilePath();
            Reader reader = Files.newBufferedReader(Paths.get(filePath));
            CSVParser csvParser = new CSVParser(reader, CSVFormat.newFormat(Config.getDelimiter().charAt(0)));

            long insertStartTime = System.currentTimeMillis();

            for (CSVRecord csvRecord : csvParser) {
                indexTuple++;
                pstmt.setTimestamp(1, java.sql.Timestamp.valueOf(csvRecord.get(0)));
                pstmt.setObject(2, csvRecord.get(1));
                pstmt.setObject(3, csvRecord.get(2));
                pstmt.setObject(4, csvRecord.get(3));
                pstmt.setObject(5, csvRecord.get(4));
                pstmt.setObject(6, csvRecord.get(5));

                pstmt.addBatch();
                if (indexTuple % batchSize == 0) {
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();
                    System.out.println("入库 " + indexTuple + " 条耗时" + (System.currentTimeMillis() - insertStartTime) + "ms" + "\n");
                }
            }
            pstmt.addBatch();
            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
            System.out.println("全部数据已入库，总耗时:" + (System.currentTimeMillis() - insertStartTime) + "ms");

        } catch (Exception e) {
            e.printStackTrace();
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("connection close error " + e.getMessage());
            }
        }
    }
}
